##!/usr/bin/env python
##-*- coding: utf-8 -*-
"""

*    Fireestatisticas.py
*    This script makes reports from ufw firewall log
*    Statistics type can be pdf 
*    Mestrado em Engenharia de Segurança Informática
*    Escola Superior de tecnologia e Gestão de Beja
*    
*    Copyright 2013 Andreia Graça <198912823@alunos.ipbeja.pt>
*    Copyright 2013 Palmira Martins <198913316@alunos.ipbeja.pt>
*    
*    This program is an academic software.
*    Only for academic purposes. Under free GNU Licences.
"""




    
import sys #system calls
import re # re.search
import GeoIP #GeoIP
import sqlite3 #database sqlite3
import datetime 
import optparse #arguments/option parse
import os
import socket
import string
#to PDF Report
from reportlab.lib.enums import TA_JUSTIFY
from reportlab.lib.pagesizes import letter
from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer, Image
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib.units import inch
from reportlab.graphics.shapes import Drawing, String#, _DrawingEditorMixin
from reportlab.graphics.charts.barcharts import VerticalBarChart  
from reportlab.graphics.charts.textlabels import Label 

##Open GEOIP data files for ipv4
gic = GeoIP.open('./Geo/GeoIPCity.dat', GeoIP.GEOIP_STANDARD)
##Open GEOIP data files for ipv6
giv6 = GeoIP.open('./Geo/GeoIPv6.dat', GeoIP.GEOIP_MEMORY_CACHE)

def openLog(logfile):
    """
    Function openLog(logfile)
    Receive argument log file ant try to open in read mode
    Exception if could not open the file
    """
    try:
        log=open(logfile, 'r')
        return log
    except Exception, e:
        print "[-] " + str(e)
        sys.exit()

def readLog(log):
    """
    Function readLog(log)
    Read log file and save the important fields in a sqlite3 database
    """
    con=createDb()
    con.cursor()
    con.execute("DROP TABLE IF EXISTS GEOIP")
    con.execute("CREATE TABLE GEOIP(Id integer,Time DATA ,IPsrc TEXT,EventSrc TEXT,Device TEXT,Protocol TEXT,TTL TEXT, SRCPort TEXT, DSTPort TEXT,Country TEXT,CountryName TEXT,Lon TEXT,Lat TEXT, CheckBL TEXT)")
    id=1
    
    for line in log.readlines():
        debug = 0
        if not re.search("SRC=192", line) and not re.search("SRC=0", line) and not re.search("SRC=172", line):
            #IP SRC
            lista = line.split("SRC=")
            IP = lista[1].split(' ')[0]
            #DATA
            lineMonth = str(line)
            dataT = lineMonth[:15]
            #INTERFACE
            device = line.split("IN=")
            dInterface = device[1].split(' ')[0]
            if (str(dInterface) ==""):
                device = line.split("OUT=")
                dInterface = device[1].split(' ')[0]
                #EVENT SOURCE
                event = line.split("IN= ")
                eventSrc = event[1].split('=')[0]
            else:
                eventSrc = "IN"

            #PROTO
            proto=line.split("PROTO=")
            protoInf=proto[1].split(' ')[0]

            try:
                if len(IP)<=15:
                    #SOURCE PORT
                    spt=line.split("SPT=")
                    sptPort=spt[1].split(' ')[0]
                    #DESTINATION PORT
                    dpt=line.split("DPT=")
                    dptPort=dpt[1].split(' ')[0]
                    data=gic.record_by_addr(IP)
                    cty = data['country_code']
                    ctyName =data['country_name']
                    longitude = data['longitude']
                    latitude = data['latitude']
                    ttlinf=line.split("TTL=")
                    ttlData=str(ttlinf[1].split(' ')[0])
                else:
                    cty=giv6.country_code_by_addr_v6(IP)
                    
                con.execute("INSERT INTO GEOIP VALUES (:Id,:Time,:IPsrc,:EventSrc,:Device,:Protocol,:TTL,:SRCPort,:DSTPort,:Country,:CountryName,:Lon,:Lat,:CheckBL)", {"Id":id,"Time":dataT,"IPsrc":IP,"EventSrc":eventSrc,"Device":dInterface,"Protocol":protoInf,"TTL":ttlData,"SRCPort":sptPort,"DSTPort":dptPort,"Country":cty,"CountryName":ctyName,"Lon":longitude,"Lat":latitude,"CheckBL":""})
                id+=1

            except:
                continue            
    con.commit()
   
    if con:
        con.close()

def createDb():
    """
    Function createDb()
    Creates a sqlite database in directory "./db/" with "ldp.db" name
    Exception lite.Error if cannot create the database
    """
    try:
        con = sqlite3.connect('./db/lpd.db')
        cur = con.cursor()
        return con
    except lite.Error, e:
        print "Error %s:" % e.args[0]
        sys.exit(1)

def createPdfReport():
    """
    Function createPdfReport() 
    Creates the PDF Report with recursion of reportlab
    
"""

    con = sqlite3.connect('./db/lpd.db') #connection to database
    cur = con.cursor() # connection cursor
    now = datetime.datetime.now() #datetime for filename
    pdfFileName="FireEstatisticas_%s.%s.pdf" % ( now.month, now.year)
    doc = SimpleDocTemplate(pdfFileName,pagesize=letter,rightMargin=72,leftMargin=72,topMargin=18,bottomMargin=72)
    Story=[]
    logoMESI = "./img/logo.png"
    im = Image(logoMESI, 7*inch, 1.3*inch)
    Story.append(im)
    Story.append(Spacer(1, 48)) 
    styles=getSampleStyleSheet()
    styles.add(ParagraphStyle(name='Justify', alignment=TA_JUSTIFY))
    ptext = '<font size=12>Date: %s, %s-%s-%s %s:%s:%s</font>' % (now.strftime("%A"), now.day, now.month, now.year, now.hour, now.minute, now.second)
    Story.append(Paragraph(ptext, styles["Normal"]))
    Story.append(Spacer(1, 24))
    cur.execute("SELECT Time FROM GEOIP ORDER BY Time ASC LIMIT 1")
    initDate = cur.fetchone()
    cur.execute("SELECT Time FROM GEOIP ORDER BY Time DESC LIMIT 1")
    finalDate = cur.fetchone()
    ptext = "<font size=16>Period from <font size=16 color ='black'>%s</font> to <font size=16 color ='black'>%s</font></font>" % (initDate[0], finalDate[0])
    Story.append(Paragraph(ptext, styles["Normal"]))
    Story.append(Spacer(1, 12))
    cur.execute("SELECT COUNT(ID) FROM GEOIP") #Query for count total events record
    total = cur.fetchone()
    ptext = "<font size=14 color='red'>%s events records" % total
    Story.append(Paragraph(ptext, styles["Normal"]))
    Story.append(Spacer(1, 12))
    ##########################
    #query for ip total distinct
    cur.execute("SELECT COUNT(DISTINCT IPsrc) FROM GEOIP")
    srcDistinct=cur.fetchone()
    ptext = "<font size=12>- IP SRC: %s distinct records</font>" % srcDistinct
    Story.append(Paragraph(ptext, styles["Normal"]))
    Story.append(Spacer(1, 4))
    ##########################
    #query for src stats
    cur.execute("SELECT DISTINCT IPsrc, COUNT(*), CountryName FROM GEOIP GROUP BY IPsrc")
    srcList = cur.fetchall()
    srcIP = map(getFirst, srcList)
    srcStat = map(getSecond, srcList)
    srcCountry = map(getThird, srcList)
    i=0
    for x in srcIP:
        result = (float(srcStat[i])/float(total[0]))*100
        cur.execute("SELECT DISTINCT CheckBL FROM GEOIP WHERE IPsrc=(:IP)",{"IP":x})
        info = cur.fetchone()
        if info[0]==" ":
            ptext = "<font size=8>   Ip %30s   --   num of occurances: %s     --   country: %s  <font size=8 color='red'>%s</font></font>" % (x, str(srcStat[i]), srcCountry[i], info[0])
        else:
            ptext = "<font size=8>   Ip %30s   --   num of occurances: %s     --   country: %s  <font size=8 color='green'>%s</font></font>" % (x, str(srcStat[i]), srcCountry[i], info[0])
        i+=1
        Story.append(Paragraph(ptext, styles["Normal"]))
        Story.append(Spacer(1, 1))
    Story.append(Spacer(1, 8))
   
    #################################
    #Query for protocol total distinct
    cur.execute("SELECT COUNT(DISTINCT Protocol) FROM GEOIP")
    protoDistinct=cur.fetchone()
    ptext = "<font size=12>- Protocol: %s distinct records " % protoDistinct
    Story.append(Paragraph(ptext, styles["Normal"]))
    Story.append(Spacer(1, 4))
    ########################
    #Query for Protocol stats
    cur.execute("SELECT DISTINCT Protocol, COUNT(*), Protocol FROM GEOIP GROUP BY Protocol")
    protoList = cur.fetchall()
    protoType = map(getFirst, protoList)
    protoStat = map(getSecond, protoList)
    i=0
    for x in protoList:
        result = (float(protoStat[i])/float(total[0]))*100
        ptext = "<font size=8>   Protocol: %s   --   num of occurances: %s  </font>" % (protoType[i], str(protoStat[i]))
        i+=1
        Story.append(Paragraph(ptext, styles["Normal"]))
        Story.append(Spacer(1, 1))
    Story.append(Spacer(1, 8))
    #############################################
    #Query for Device and EventSrc total distinct
    cur.execute("SELECT COUNT(DISTINCT EventSrc) FROM GEOIP")
    DeviceDistinct=cur.fetchone()
    ptext = "<font size=12>- Event Source: %s distinct records " % DeviceDistinct
    Story.append(Paragraph(ptext, styles["Normal"]))
    Story.append(Spacer(1, 4))
    ####################################
    #Query for EventSrc stats
    cur.execute("SELECT DISTINCT EventSrc, COUNT(*) FROM GEOIP GROUP BY EventSrc")
    EventList = cur.fetchall()
    for EventSrc,EventStat in EventList:
        result = (float(EventStat)/float(total[0]))*100
        ptext = "<font size=8>   Event Source: %s   --   num of occurances: %s  </font>" % (EventSrc, EventStat)
        Story.append(Paragraph(ptext, styles["Normal"]))
        Story.append(Spacer(1, 1))
    Story.append(Spacer(1, 8))
    #############################################
    #Query for distinct Destination Port by IPsrc
    cur.execute("SELECT COUNT(DISTINCT  DSTPort || IPsrc) FROM GEOIP")
    sptDistinct=cur.fetchone()
    ptext = "<font size=12>- Destination Ports events: %s distinct records " % sptDistinct
    Story.append(Paragraph(ptext, styles["Normal"]))
    Story.append(Spacer(1, 4))
    ####################################
    #Query for Destination Port by IPsrc
    cur.execute("SELECT DISTINCT DSTPort, IPsrc, COUNT(*) FROM GEOIP GROUP BY IPsrc || DSTPort")
    DSTList = cur.fetchall()
    for dstport,fromip,dststat in DSTList:
        result = (float(dststat)/float(total[0]))*100
        ptext = "<font size=8>   Destination Port: %s   --   event from: %s   --   num of occurances: %s  </font>" % (dstport,fromip,dststat)
        Story.append(Paragraph(ptext, styles["Normal"]))
        Story.append(Spacer(1, 1))
    Story.append(Spacer(1, 8))
    Story.append(Spacer(1, 100))
       
    #################################
    #Vertical Bar Chart for Estatisticas de Eventos
    d = Drawing(100,0)
    barChartSrc = VerticalBarChart()
    d.add(String(380,50,'Estatisticas de Eventos'), name='title')
    Story.append(d)
    barChartSrc.width = 200
    barChartSrc.height = 80
    barChartSrc.x = 300#20
    barChartSrc.y = -40#25
    barChartSrc.data = [(srcStat),]
    barChartSrc.categoryAxis.categoryNames = srcIP
    barChartSrc.barSpacing                 = 2
    barChartSrc.groupSpacing               = 2   
    barChartSrc.valueAxis.labels.fontName  = 'Helvetica'   
    barChartSrc.valueAxis.labels.fontSize  = 8
    barChartSrc.categoryAxis.labels.fontSize  = 8
    barChartSrc.categoryAxis.labels.dx  =  -14  
    barChartSrc.categoryAxis.labels.dy  = -20   
    barChartSrc.categoryAxis.labels.angle  = 50
    barChartSrc.barLabels.fontSize         = 8  
    barChartSrc.valueAxis.forceZero        = 1 
    barChartSrc.valueAxis.rangeRound       = 'both'
    d.add(barChartSrc)
    Story.append(d)
    #################################
 
   
    Story.append(Spacer(1, 100))
    ptext = '<font size=12></font>'
    Story.append(Paragraph(ptext, styles["Normal"]))
    doc.build(Story)
    if con:
        con.close()
    print "Done!\nPdf file created with filename "+ pdfFileName

def getFirst(k):
    """
    Function getFirst(k) to return first element on list - for map calls
    """
    x, y, z = k 
    return x
    
def getSecond(k):
    """
    Function getSecond(k) to return second element on list - for map calls
    """
    x, y, z = k 
    return y

#function to return third element on list
def getThird(k):
    """
    Function getThird(k) to return third element on list - for map calls
    """
    x, y, z = k 
    return z


def main():
    """
    main funtion
    first openLog file function and readLog function
    call function createPdfReport()
    arguments obtained with optparse
    """
    parser = optparse.OptionParser(usage="Usage: %prog " +"-f <log file> -p (export to pdf)", version='%LPD 0.01 - MESI2013')
    parser.add_option("-f", dest="file", type="string", help="name of the log file")
    parser.add_option("-p", "--pdf", action="store_true", dest="pdf", help="create a Report in PDF file")
   
    (options, args) = parser.parse_args()

    if options.file:
        print "Processing... (please wait)"
        savelog=openLog(options.file)
        readLog(savelog)
    else:
        print "You must specify a log file."
        print ("Usage: python FireEstatisticas.py -f ufw.log -p ")
        exit(0)

    if options.pdf == True:
        createPdfReport()
   

if __name__ == '__main__':
    main()
